import pandas as pd
import numpy as np
from docx import Document
from docx.shared import Pt
from docx.table import _Cell as Cell

import os


def get_timestr()->str:
    '''
    生成日期时间字符串
    >>> get_timestr()
    202312190844
    '''
    return pd.Timestamp.now().strftime('%Y%m%d%H%M%S')
# Read the Excel file and extract data

def create_output_dir(pre:str):
    '''在当前目录创建以 pre 为文件名的子目录，并返回目录名'''
    dir = pre + '_' + get_timestr()
    if not os.path.exists(dir):
        os.mkdir(dir)
    return dir
# Read the Excel file and extract data
    
def merge_dir_file(dir, file):
    '''
    dir: 当前目录下子目录名
    file: 要放在 dir 目录的文件名
    生成完整路径
    ''' 
    return os.path.join(dir, file)

def extract_data(fxl):
    xl = pd.read_excel(fxl, skiprows=6)
    # 生成从 A 到 R 的 list
    cols = [chr(i) for i in range(ord('A'), ord('R') + 1)]
    # xl = xl[cols]

    return xl

def gen_docx_name(row:pd.Series):
    return f"{row.name+1:0{3}d}" + '_' + row.B + '_' + row.A + '.docx'


def format_data(k, v):
    value = '' if (v is np.nan) or (str(v).strip()=='') or ('nan' in str(v)) else v
    if isinstance(value, (int,float)) and k=='C':
        value = f'{value:.0f}'
    elif isinstance(value, (int, float)) and k!='C':
        value = f'{value:.2f}'
        
    return value

def format_cell(cell:Cell, fontname='宋体', size=8, alignment=1):
    # cell 设为宋体
    cell.paragraphs[0].runs[0].font.name = fontname
    # cell 设置字体为 8
    cell.paragraphs[0].runs[0].font.size = Pt(size)
    # cell 居中
    cell.paragraphs[0].alignment = alignment     
    
def fill_word_document(row, template, fdoc):
    doc = Document(template)
    # Find and replace placeholders in the Word document
    # for paragraph in doc.paragraphs:
    #     for idx in row.index:
    #         placeholder = '{{' + idx + '}}'
    #         if placeholder in paragraph.text:
    #             paragraph.text = paragraph.text.replace(placeholder, str(row[idx]))


    # Update fields in tables if needed
    for table in doc.tables:
        for drow in table.rows:
            height = drow.height
            for cell in drow.cells:               
                for idx, val in row.items():
                    placeholder = '{{' + str(idx) + '}}'                    
                    value = format_data(idx, val)
                    if placeholder in cell.text:                           
                        cell.text = cell.text.replace(placeholder, value)   
                        if placeholder in ['{{A}}' , '{{B}}']:
                            # print(f'value:{cell.text}, format{placeholder}')
                            format_cell(cell, alignment=0)
                        else:                
                            format_cell(cell)
            drow.height = height
            
    # 调整 doc 底边距
    # doc.sections[0].top_margin = Pt(24)
    doc.sections[0].bottom_margin = Pt(49)
    # doc.sections[0].left_margin = Pt(24)
    # doc.sections[0].right_margin = Pt(24)                    

    print(f'正在生成 word 文件{fdoc}...')
    doc.save(fdoc)

def batch_filling(fxl, template):
    xl = extract_data(fxl)
    dir = create_output_dir('lo')
    for i, row in xl.iterrows():
        fdoc = gen_docx_name(row)
        fdoc = merge_dir_file(dir, fdoc)
        fill_word_document(row, template, fdoc)    
    
if  __name__ == '__main__':
 
    fxl = '总表.xlsx'
    template = 'template.docx'

    batch_filling(fxl, template)

    # row = xl.iloc[0, :]
    # fdoc = gen_docx_name(row)
    # fdoc = merge_dir_file(dir, fdoc)
    # fill_word_document(row, template, fdoc)

    print('已执行完毕.')



